package com.wongoing.sys.util.excel;

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import com.wongoing.sys.util.Encodes;
import com.wongoing.util.NumberUtil;
public class ExportExcelMultiSheet {

    private static HSSFWorkbook wb;

    private static CellStyle titleStyle; // 标题行样式
    private static Font titleFont; // 标题行字体
    private static CellStyle dateStyle; // 日期行样式
    private static Font dateFont; // 日期行字体
    private static CellStyle headStyle; // 表头行样式
    private static Font headFont; // 表头行字体
    private static CellStyle contentStyle; // 内容行样式
    private static Font contentFont; // 内容行字体

    /**
     * 导出文件
     */
    public static boolean export2File(ExcelExportData setInfo,
            String outputExcelFileName) throws Exception {
        return ExcelFileUtil.write(outputExcelFileName, export2ByteArray(setInfo),
                true, true);
    }
    
    /**
     * 导出到浏览器客户端
     */
    public static void export2Client(HttpServletResponse response, String fileName, ExcelExportData setInfo)
            throws Exception {
    	response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
		
		OutputStream toClient = new BufferedOutputStream(response.getOutputStream()); 
		toClient.write(ExportExcelMultiSheet.export2ByteArray(setInfo));  
        toClient.flush();  
        toClient.close(); 
    }

    /**
     * 导出到byte数组
     */
    public static byte[] export2ByteArray(ExcelExportData setInfo)
            throws Exception {
        return export2Stream(setInfo).toByteArray();
    }

    /**
     * 导出到流
     */
    public static ByteArrayOutputStream export2Stream(ExcelExportData setInfo)
            throws Exception {
        init();
        
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        Set<Entry<String, List<?>>> set = setInfo.getDataMap().entrySet();
        String[] sheetNames = new String[setInfo.getDataMap().size()];
        int sheetNameNum = 0;
        for (Entry<String, List<?>> entry : set) {
            sheetNames[sheetNameNum] = entry.getKey();
            sheetNameNum++;
        }
        HSSFSheet[] sheets = getSheets(setInfo.getDataMap().size(), sheetNames);
        int sheetNum = 0;
        int k = 0;
        
        for (Entry<String, List<?>> entry : set) {
            // Sheet
            List<?> objs = entry.getValue();

            // 标题行
            createTableTitleRow(setInfo, sheets, sheetNum);

            // 日期行
            createTableDateRow(setInfo, sheets, sheetNum);

            // 表头
            creatTableHeadRow(setInfo, sheets, sheetNum);

            // 表体
            String[] fieldNames = setInfo.getFieldNames().get(sheetNum);
            
            int rowNum = 3;
            for (Object obj : objs) {
                HSSFRow contentRow = sheets[sheetNum].createRow(rowNum);
                contentRow.setHeight((short) 300);
                HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames().get(sheetNum).length);
                int cellNum = 1; // 去掉一列序号，因此从1开始
                if (fieldNames != null) {
                    for (int num = 0; num < fieldNames.length; num++) {
                        Object value = ExcelReflection.invokeGetterMethod(obj,fieldNames[num]);
                        Boolean isNum = false;//data是否为数值型
                       /* Boolean isInteger=false;//data是否为整数
*/                       
                        if (value != null || "".equals(value)) {
                            //判断data是否为数值型
                        	isNum = value.toString().matches("^(\\-)?\\d+(\\.\\d{2})$");
                           /* isNum = value.toString().matches("^(-?\\d+)(\\.\\d+)?$");*/
                            //判断data是否为整数（小数部分是否为0）
                           /* isInteger=value.toString().matches("^[-\\+]?[\\d]*$");*/
                            //判断data是否为百分数（是否包含“%”）
                            /*isPercent=value.toString().contains("%");*/
                        }
                        if (isNum) {
                        	
                        	cells[cellNum].setCellValue(Double.valueOf(value.toString()));
                        	/*BigDecimal bd = new BigDecimal(value.toString());
                        	cells[cellNum].setCellValue(bd.doubleValue());*/
                        	/*cells[cellNum].setCellValue(Double.valueOf(value.toString()));*/
                        	/*DecimalFormat df= new DecimalFormat("#######0.00");
                            Double values=new Double(df.format(value).toString());
                            cells[cellNum].setCellValue(values);*/
                        	/*cells[cellNum].setCellValue(df.format(Double.valueOf(value.toString())).toString());*/
                        	/*BigDecimal bd = new BigDecimal(value.toString());
                        	double d1 = bd.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                        	double values =new BigDecimal(value.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue(); 
                        	double values=NumberUtil.doubleFormat(2,d1);
                        	cells[cellNum].setCellValue(values);*/
                        	/* HSSFCellStyle cellStyle= wb.createCellStyle();
                        	 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.0#"));
                        	 cells[cellNum].setCellStyle(cellStyle);*/
                            }else {
                        cells[cellNum].setCellValue(value == null ? "" : value.toString());}
                        cellNum++;
                    }
                }
                rowNum++;
            }
            
            k++;
            String[] groupColumns = null;
            if(setInfo.getGroupColumn().size()!=0){
                if(setInfo.getGroupColumn().size() >= k){
                    groupColumns = setInfo.getGroupColumn().get(sheetNum);
                }
            }
            
            if(groupColumns!=null){
                int n=0;
                for (int i = 0; i < groupColumns.length; i++) {
                    
                    String[] fieldName = setInfo.getFieldNames().get(sheetNum);
                    for (int j = 0; j < fieldName.length; j++) {
                        if(groupColumns[i].equals(fieldName[j])){
                            j++;
                            n=j;
                            break;
                        }
                    }
                    int x = 0;
                    int y = 0;
                    int z = 3;
                    int m = objs.size();
                    boolean flag = false;
                    Object val = null;
                    CellRangeAddress dateRange = null;
                    for (Object obj1 : objs) {
                        y++;
                        Object value = ExcelReflection.invokeGetterMethod(obj1,groupColumns[i]);
                        if(x==0){
                            x++;
                            val=value;
                        }else if(val.toString().equals(value.toString())){
                            x++;
                            if(m==y){
                                dateRange = new CellRangeAddress(z, x+3, n, n);
                                sheets[sheetNum].addMergedRegion(dateRange);
                            }
                        }else{
                            val=value;
                            if(flag){
                                dateRange = new CellRangeAddress(z, x+3, n, n);
                                z=x+4;
                                x=x+1;
                            }else{
                                dateRange = new CellRangeAddress(z, x+2, n, n);
                                z=x+3;
                            }
                            sheets[sheetNum].addMergedRegion(dateRange);
                            flag=true;
                        }
                    }
                }
            }
            
//            CellRangeAddress dateRange = new CellRangeAddress(3, 10, 1, 1);
//            sheets[sheetNum].addMergedRegion(dateRange);
//            
//            CellRangeAddress aa = new CellRangeAddress(11, 15, 1, 1);
//            sheets[sheetNum].addMergedRegion(aa);
//            
//            CellRangeAddress bb = new CellRangeAddress(3, 5, 2, 2);
//            sheets[sheetNum].addMergedRegion(bb);
            
//            
//            CellRangeAddress aaa = new CellRangeAddress(16, 18, 1, 1);
//            sheets[sheetNum].addMergedRegion(aaa);
            
            adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽
            sheetNum++;
        }
        wb.write(outputStream);
        return outputStream;
    }
    
    /**
     * @Description: 初始化
     */
    private static void init() {
        wb = new HSSFWorkbook();

        titleFont = wb.createFont();
        titleStyle = wb.createCellStyle();
        dateStyle = wb.createCellStyle();
        dateFont = wb.createFont();
        headStyle = wb.createCellStyle();
        headFont = wb.createFont();
        contentStyle = wb.createCellStyle();
        contentFont = wb.createFont();

        initTitleCellStyle();
        initTitleFont();
        initDateCellStyle();
        initDateFont();
        initHeadCellStyle();
        initHeadFont();
        initContentCellStyle();
        initContentFont();
    }

    /**
     * @Description: 自动调整列宽
     */
    private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum,
            String[] fieldNames) {
        for (int i = 0; i < fieldNames.length + 1; i++) {
            sheets[sheetNum].autoSizeColumn(i, true);
        }
    }

    /**
     * @Description: 创建标题行(需合并单元格)
     */
    private static void createTableTitleRow(ExcelExportData setInfo, HSSFSheet[] sheets, int sheetNum) {
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo.getFieldNames().get(sheetNum).length);
        sheets[sheetNum].addMergedRegion(titleRange);
        HSSFRow titleRow = sheets[sheetNum].createRow(0);
        titleRow.setHeight((short) 800);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue(setInfo.getTitles()[sheetNum]);
    }

    /**
     * @Description: 创建日期行(需合并单元格)
     */
    private static void createTableDateRow(ExcelExportData setInfo,HSSFSheet[] sheets, int sheetNum) {
        CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo.getFieldNames().get(sheetNum).length);
        sheets[sheetNum].addMergedRegion(dateRange);
        HSSFRow dateRow = sheets[sheetNum].createRow(1);
        dateRow.setHeight((short) 350);
        HSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellStyle(dateStyle);
        dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
    }

    /**
     * @Description: 创建表头行(需合并单元格)
     */
    private static void creatTableHeadRow(ExcelExportData setInfo,
            HSSFSheet[] sheets, int sheetNum) {
        // 表头
        HSSFRow headRow = sheets[sheetNum].createRow(2);
        headRow.setHeight((short) 350);
        // 序号列
        HSSFCell snCell = headRow.createCell(0);
        snCell.setCellStyle(headStyle);
        snCell.setCellValue("序号");
        // 列头名称
        for (int num = 1, len = setInfo.getColumnNames().get(sheetNum).length; num <= len; num++) {
            HSSFCell headCell = headRow.createCell(num);
            headCell.setCellStyle(headStyle);
            headCell.setCellValue(setInfo.getColumnNames().get(sheetNum)[num - 1]);
        }
    }

    /**
     * @Description: 创建所有的Sheet
     */
    private static HSSFSheet[] getSheets(int num, String[] names) {
        HSSFSheet[] sheets = new HSSFSheet[num];
        for (int i = 0; i < num; i++) {
            sheets[i] = wb.createSheet(names[i]);
        }
        return sheets;
    }

    /**
     * @Description: 创建内容行的每一列(附加一列序号)
     */
    private static HSSFCell[] getCells(HSSFRow contentRow, int num) {
        HSSFCell[] cells = new HSSFCell[num + 1];
       
        for (int i = 0, len = cells.length; i < len; i++) {
            cells[i] = contentRow.createCell(i);
            cells[i].setCellStyle(contentStyle);
        }

        // 设置序号列值，因为出去标题行和日期行，所有-2
        cells[0].setCellValue(contentRow.getRowNum() - 2);

        return cells;
    }

    /**
     * @Description: 初始化标题行样式
     */
    private static void initTitleCellStyle() {
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle.setFont(titleFont);
        titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
    }

    /**
     * @Description: 初始化日期行样式
     */
    private static void initDateCellStyle() {
        dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        dateStyle.setFont(dateFont);
        dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
    }

    /**
     * @Description: 初始化表头行样式
     */
    private static void initHeadCellStyle() {
        headStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headStyle.setFont(headFont);
        headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
        headStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headStyle.setBorderRight(CellStyle.BORDER_THIN);
        headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
        headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
        headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
        headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
    }

    /**
     * @Description: 初始化内容行样式
     */
    private static void initContentCellStyle() {
        contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentStyle.setFont(contentFont);
        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
        contentStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
        contentStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
        contentStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
        contentStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
        contentStyle.setWrapText(true); // 字段换行
    }

    /**
     * @Description: 初始化标题行字体
     */
    private static void initTitleFont() {
        titleFont.setFontName("华文楷体");
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setCharSet(Font.DEFAULT_CHARSET);
        titleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
    }

    /**
     * @Description: 初始化日期行字体
     */
    private static void initDateFont() {
        dateFont.setFontName("隶书");
        dateFont.setFontHeightInPoints((short) 10);
        dateFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        dateFont.setCharSet(Font.DEFAULT_CHARSET);
        dateFont.setColor(IndexedColors.BLUE_GREY.getIndex());
    }

    /**
     * @Description: 初始化表头行字体
     */
    private static void initHeadFont() {
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 10);
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headFont.setCharSet(Font.DEFAULT_CHARSET);
        headFont.setColor(IndexedColors.BLUE_GREY.getIndex());
    }

    /**
     * @Description: 初始化内容行字体
     */
    private static void initContentFont() {
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        contentFont.setCharSet(Font.DEFAULT_CHARSET);
        contentFont.setColor(IndexedColors.BLUE_GREY.getIndex());
    }

    /**
     * Excel导出数据类
     * @author jimmy
     */
    public static class ExcelExportData {

        /**
         * 导出数据 key:String 表示每个Sheet的名称 value:List<?> 表示每个Sheet里的所有数据行
         */
        private LinkedHashMap<String, List<?>> dataMap;

        /**
         * 每个Sheet里的顶部大标题
         */
        private String[] titles;

        /**
         * 单个sheet里的数据列标题
         */
        private List<String[]> columnNames;

        /**
         * 单个sheet里每行数据的列对应的对象属性名称
         */
        private List<String[]> fieldNames;

        private List<String[]> groupColumn;
        
        public List<String[]> getFieldNames() {
            return fieldNames;
        }

        public void setFieldNames(List<String[]> fieldNames) {
            this.fieldNames = fieldNames;
        }

        public String[] getTitles() {
            return titles;
        }

        public void setTitles(String[] titles) {
            this.titles = titles;
        }

        public List<String[]> getColumnNames() {
            return columnNames;
        }

        public void setColumnNames(List<String[]> columnNames) {
            this.columnNames = columnNames;
        }

        public LinkedHashMap<String, List<?>> getDataMap() {
            return dataMap;
        }

        public void setDataMap(LinkedHashMap<String, List<?>> dataMap) {
            this.dataMap = dataMap;
        }

        public List<String[]> getGroupColumn() {
            return groupColumn;
        }

        public void setGroupColumn(List<String[]> groupColumn) {
            this.groupColumn = groupColumn;
        }
    }
    
}